﻿/*
#------------------------------------------------------------------------------
#-- Program Name:	[dbo].[spMSX_rebuild_central_server_list]
#-- Purpose:		Rebuilds msdb.dbo.sysmanagement_shared_registered_servers_internal
#--					and msdb.dbo.sysmanagement_shared_server_groups_internal
#--					based on the information stored in tblMSX_server_discovery
#--	Last Update:	04/28/2016
#--					For a complete history - please review comments in Version
#--					Control.
#------------------------------------------------------------------------------
*/
CREATE PROCEDURE [dbo].[spMSX_rebuild_central_server_list]
AS

SET NOCOUNT ON

--- Declare Local Variables
DECLARE @group2000P int, @group2000Q int, @group2000T int, @group2000Unk int,
		@groupP int, @groupQ int, @groupT int, @groupUnk int,
		@groupOther int

--- Working tables
PRINT 'Creating temporary tables'
CREATE TABLE #centraldba_server_groups
(
	server_group_id				int				IDENTITY(6, 1) PRIMARY KEY,
	name						sysname			NOT NULL,
	description					nvarchar(2048)	NOT NULL,
	server_type					int				NOT NULL,
	parent_id					int,
	is_system_object			bit				DEFAULT(0)
)
CREATE TABLE #centraldba_servers
(
	server_id					int				IDENTITY(1, 1) PRIMARY KEY,
	server_group_id				int,
	name						sysname			NOT NULL,
	server_name					sysname			NOT NULL,
	description					nvarchar(2048)	NOT NULL,
	server_type					int				NOT NULL
)

BEGIN TRY
	PRINT 'Building an updated list of servers'
	
	--- Create the Groups (Application then Server List)
	INSERT INTO #centraldba_server_groups (name, description, server_type, parent_id, is_system_object)
		VALUES (N'Applications', N'', 0, 1, 0)

	INSERT INTO #centraldba_server_groups (name, description, server_type, parent_id, is_system_object)
		SELECT		DISTINCT
					(CASE WHEN ISNULL(app.[app_name], SPACE(0)) = SPACE(0) THEN 'Unknown' ELSE app.[app_name] END) name,
					N'' description,
					0 server_type,
					(
					SELECT		server_group_id
					FROM		#centraldba_server_groups
					WHERE		name = N'Applications'
								AND ISNULL(parent_id, 0) = 1
					) parent_id,
					0 is_system_object
		FROM		[dbo].[tblMSX_server_discovery] s
		JOIN		[dbo].[tblMSX_server_sql_info] sq ON s.ssd_id = sq.sql_ssd_id
		JOIN		[dbo].[tblMSX_application] app ON s.[ssd_app_id] = app.[app_id]
		LEFT JOIN	[dbo].[tblMSX_sql_version] sv ON sq.sql_version_major_ro = sv.ver_major_ro
					AND sq.sql_version_minor_ro = sv.ver_minor_ro
		WHERE		s.ssd_is_disabled = 0
					AND s.ssd_is_vendor_managed_ro = 0

	--- Insert the Servers into the Groups (Application then Server List)
	INSERT INTO #centraldba_servers (server_group_id, name, server_name, description, server_type)
		SELECT	(
				SELECT		t2.server_group_id
				FROM		#centraldba_server_groups t1
				JOIN		#centraldba_server_groups t2 ON t1.server_group_id = t2.parent_id
				WHERE		t1.name = N'Applications'
							AND ISNULL(t1.parent_id, 0) = 1
							AND t2.name = CAST(ISNULL(tServ.sql_description, SPACE(0)) as nvarchar(128))
				) server_group_id, 
				ssd_type_ro + ' - ' + ssd_sqlserver_name_ro name, 
				ssd_sqlserver_name_ro server_name, 
				ISNULL(sql_description, SPACE(0)) description,
				0 server_type
		FROM	(
				SELECT 		s.ssd_type_ro,
							(CASE WHEN ISNULL(app.[app_name], SPACE(0)) = SPACE(0) THEN 'Unknown' ELSE app.[app_name] END) SQLServer_description,
							s.ssd_sqlserver_name_ro,
							app.[app_name] sql_description
				FROM		[dbo].[tblMSX_server_discovery] s
				JOIN		[dbo].[tblMSX_server_sql_info] sq ON s.ssd_id = sq.sql_ssd_id
				JOIN		[dbo].[tblMSX_application] app ON s.[ssd_app_id] = app.[app_id]
				LEFT JOIN	[dbo].[tblMSX_sql_version] sv ON sq.sql_version_major_ro = sv.ver_major_ro
							AND sq.sql_version_minor_ro = sv.ver_minor_ro
				WHERE		s.ssd_is_disabled = 0
							AND s.ssd_is_vendor_managed_ro = 0
				) tServ


	--- Create the Groups (Environment then SQL Version)
	INSERT INTO #centraldba_server_groups (name, description, server_type, parent_id, is_system_object)
		VALUES (N'Environment', N'', 0, 1, 0)
	INSERT INTO #centraldba_server_groups (name, description, server_type, parent_id, is_system_object)
		SELECT		DISTINCT
					ssd_type_ro name,
					N'' description,
					0 server_type,
					(
					SELECT		server_group_id
					FROM		#centraldba_server_groups
					WHERE		name = N'Environment'
								AND ISNULL(parent_id, 0) = 1
					) parent_id,
					0 is_system_object
		FROM		[dbo].[tblMSX_server_discovery] s
		JOIN		[dbo].[tblMSX_server_sql_info] sq ON s.ssd_id = sq.sql_ssd_id
		LEFT JOIN	[dbo].[tblMSX_sql_version] sv ON sq.sql_version_major_ro = sv.ver_major_ro
					AND sq.sql_version_minor_ro = sv.ver_minor_ro
		WHERE		s.ssd_is_disabled = 0
					AND s.ssd_is_vendor_managed_ro = 0
	INSERT INTO #centraldba_server_groups (name, description, server_type, parent_id, is_system_object)
		SELECT 		DISTINCT
					(CASE WHEN ISNULL(sv.ver_description, SPACE(0)) = SPACE(0) THEN 'Unknown' ELSE sv.ver_description END) name,
					N'' description,
					0 server_type,
					(
					SELECT		t2.server_group_id
					FROM		#centraldba_server_groups t1
					JOIN		#centraldba_server_groups t2
									ON t1.server_group_id = t2.parent_id
					WHERE		t1.name = N'Environment'
								AND ISNULL(t1.parent_id, 0) = 1
								AND t2.name = s.ssd_type_ro
					) parent_id,
					0 is_system_object
		FROM		[dbo].[tblMSX_server_discovery] s
		JOIN		[dbo].[tblMSX_server_sql_info] sq ON s.ssd_id = sq.sql_ssd_id
		LEFT JOIN	[dbo].[tblMSX_sql_version] sv ON sq.sql_version_major_ro = sv.ver_major_ro
					AND sq.sql_version_minor_ro = sv.ver_minor_ro
		WHERE		s.ssd_is_disabled = 0
					AND s.ssd_is_vendor_managed_ro = 0

	--- Insert the Servers into the Groups (Environment then SQL Version)
	INSERT INTO #centraldba_servers (server_group_id, name, server_name, description, server_type)
		SELECT	(
				SELECT		t3.server_group_id
				FROM		#centraldba_server_groups t1
				JOIN		#centraldba_server_groups t2
								ON t1.server_group_id = t2.parent_id
				JOIN		#centraldba_server_groups t3
								ON t2.server_group_id = t3.parent_id
				WHERE		t1.name = N'Environment'
							AND ISNULL(t1.parent_id, 0) = 1
							AND t2.name = tServ.ssd_type_ro
							AND t3.name = tServ.SQLServer_description
				) server_group_id, 
				ssd_sqlserver_name_ro name, 
				ssd_sqlserver_name_ro server_name, 
				ISNULL(sql_description, SPACE(0)) description,
				0 server_type
		FROM	(
				SELECT 		s.ssd_type_ro,
							(CASE WHEN ISNULL(sv.ver_description, SPACE(0)) = SPACE(0) THEN 'Unknown' ELSE sv.ver_description END) SQLServer_description,
							s.ssd_sqlserver_name_ro,
							app.[app_name] sql_description
				FROM		[dbo].[tblMSX_server_discovery] s
				JOIN		[dbo].[tblMSX_server_sql_info] sq ON s.ssd_id = sq.sql_ssd_id
				JOIN		[dbo].[tblMSX_application] app ON s.[ssd_app_id] = app.[app_id]
				LEFT JOIN	[dbo].[tblMSX_sql_version] sv ON sq.sql_version_major_ro = sv.ver_major_ro
							AND sq.sql_version_minor_ro = sv.ver_minor_ro
				WHERE		s.ssd_is_disabled = 0
							AND s.ssd_is_vendor_managed_ro = 0
				) tServ

	--- Create the Groups (MSX Managed then SQL Version)
	INSERT INTO #centraldba_server_groups (name, description, server_type, parent_id, is_system_object)
		VALUES (N'MSX Managed', N'', 0, 1, 0)
	INSERT INTO #centraldba_server_groups (name, description, server_type, parent_id, is_system_object)
		SELECT		DISTINCT
					ssd_type_ro name,
					N'' description,
					0 server_type,
					(
					SELECT		server_group_id
					FROM		#centraldba_server_groups
					WHERE		name = N'MSX Managed'
								AND ISNULL(parent_id, 0) = 1
					) parent_id,
					0 is_system_object
		FROM		[dbo].[tblMSX_server_discovery] s
		JOIN		[dbo].[tblMSX_server_sql_info] sq ON s.ssd_id = sq.sql_ssd_id
		LEFT JOIN	[dbo].[tblMSX_sql_version] sv ON sq.sql_version_major_ro = sv.ver_major_ro
					AND sq.sql_version_minor_ro = sv.ver_minor_ro
		WHERE		s.ssd_is_disabled = 0
					AND s.ssd_is_vendor_managed_ro = 0
					AND ISNULL(sq.sql_has_master_jobs, 0) = 1
	INSERT INTO #centraldba_server_groups (name, description, server_type, parent_id, is_system_object)
		SELECT 		DISTINCT
					ISNULL(sv.ver_description, 'Unknown') name,
					N'' description,
					0 server_type,
					(
					SELECT		t2.server_group_id
					FROM		#centraldba_server_groups t1
					JOIN		#centraldba_server_groups t2
									ON t1.server_group_id = t2.parent_id
					WHERE		t1.name = N'MSX Managed'
								AND ISNULL(t1.parent_id, 0) = 1
								AND t2.name = s.ssd_type_ro
					) parent_id,
					0 is_system_object
		FROM		[dbo].[tblMSX_server_discovery] s
		JOIN		[dbo].[tblMSX_server_sql_info] sq ON s.ssd_id = sq.sql_ssd_id
		LEFT JOIN	[dbo].[tblMSX_sql_version] sv ON sq.sql_version_major_ro = sv.ver_major_ro
					AND sq.sql_version_minor_ro = sv.ver_minor_ro
		WHERE		s.ssd_is_disabled = 0
					AND s.ssd_is_vendor_managed_ro = 0
					AND ISNULL(sq.sql_has_master_jobs, 0) = 1

	--- Insert the Servers into the Groups (MSX Managed then SQL Version)
	INSERT INTO #centraldba_servers (server_group_id, name, server_name, description, server_type)
		SELECT	(
				SELECT		t3.server_group_id
				FROM		#centraldba_server_groups t1
				JOIN		#centraldba_server_groups t2
								ON t1.server_group_id = t2.parent_id
				JOIN		#centraldba_server_groups t3
								ON t2.server_group_id = t3.parent_id
				WHERE		t1.name = N'MSX Managed'
							AND ISNULL(t1.parent_id, 0) = 1
							AND t2.name = tServ.ssd_type_ro
							AND t3.name = tServ.SQLServer_description
				) server_group_id, 
				ssd_sqlserver_name_ro name, 
				ssd_sqlserver_name_ro server_name, 
				ISNULL(sql_description, SPACE(0)) description,
				0 server_type
		FROM	(
				SELECT 		s.ssd_type_ro,
							ISNULL(sv.ver_description, 'Unknown') SQLServer_description,
							s.ssd_sqlserver_name_ro,
							app.[app_name] sql_description
				FROM		[dbo].[tblMSX_server_discovery] s
				JOIN		[dbo].[tblMSX_server_sql_info] sq ON s.ssd_id = sq.sql_ssd_id
				JOIN		[dbo].[tblMSX_application] app ON s.[ssd_app_id] = app.[app_id]
				LEFT JOIN	[dbo].[tblMSX_sql_version] sv ON sq.sql_version_major_ro = sv.ver_major_ro
							AND sq.sql_version_minor_ro = sv.ver_minor_ro
				WHERE		s.ssd_is_disabled = 0
							AND s.ssd_is_vendor_managed_ro = 0
							AND ISNULL(sq.sql_has_master_jobs, 0) = 1
				) tServ
				
	--- Create the Groups (SQL Version then Environment)
	INSERT INTO #centraldba_server_groups (name, description, server_type, parent_id, is_system_object)
		VALUES (N'SQL_Version', N'', 0, 1, 0)
	INSERT INTO #centraldba_server_groups (name, description, server_type, parent_id, is_system_object)
		SELECT 		DISTINCT
					ISNULL(sv.ver_description, 'Unknown') name,
					N'' description,
					0 server_type,
					(
					SELECT		server_group_id
					FROM		#centraldba_server_groups
					WHERE		name = N'SQL_Version'
								AND ISNULL(parent_id, 0) = 1
					) parent_id,
					0 is_system_object
		FROM		[dbo].[tblMSX_server_discovery] s
		JOIN		[dbo].[tblMSX_server_sql_info] sq ON s.ssd_id = sq.sql_ssd_id
		LEFT JOIN	[dbo].[tblMSX_sql_version] sv ON sq.sql_version_major_ro = sv.ver_major_ro
					AND sq.sql_version_minor_ro = sv.ver_minor_ro
		WHERE		s.ssd_is_disabled = 0
					AND s.ssd_is_vendor_managed_ro = 0
	INSERT INTO #centraldba_server_groups (name, description, server_type, parent_id, is_system_object)
		SELECT		DISTINCT
					ssd_type_ro name,
					N'' description,
					0 server_type,
					(
					SELECT		t2.server_group_id
					FROM		#centraldba_server_groups t1
					JOIN		#centraldba_server_groups t2
									ON t1.server_group_id = t2.parent_id
					WHERE		t1.name = N'SQL_Version'
								AND ISNULL(t1.parent_id, 0) = 1
								AND t2.name = ISNULL(sv.ver_description, 'Unknown')
					) parent_id,
					0 is_system_object
		FROM		[dbo].[tblMSX_server_discovery] s
		JOIN		[dbo].[tblMSX_server_sql_info] sq ON s.ssd_id = sq.sql_ssd_id
		LEFT JOIN	[dbo].[tblMSX_sql_version] sv ON sq.sql_version_major_ro = sv.ver_major_ro
					AND sq.sql_version_minor_ro = sv.ver_minor_ro
		WHERE		s.ssd_is_disabled = 0
					AND s.ssd_is_vendor_managed_ro = 0

	--- Insert the Servers into the Groups (SQL Version then Environment)
	INSERT INTO #centraldba_servers (server_group_id, name, server_name, description, server_type)
		SELECT	(
				SELECT		t3.server_group_id
				FROM		#centraldba_server_groups t1
				JOIN		#centraldba_server_groups t2
								ON t1.server_group_id = t2.parent_id
				JOIN		#centraldba_server_groups t3
								ON t2.server_group_id = t3.parent_id
				WHERE		t1.name = N'SQL_Version'
							AND ISNULL(t1.parent_id, 0) = 1
							AND t2.name = tServ.SQLServer_description
							AND t3.name = tServ.ssd_type_ro
				) server_group_id, 
				ssd_sqlserver_name_ro name, 
				ssd_sqlserver_name_ro server_name, 
				ISNULL(sql_description, SPACE(0)) description,
				0 server_type
		FROM	(
				SELECT 		s.ssd_type_ro,
							ISNULL(sv.ver_description, 'Unknown') SQLServer_description,
							s.ssd_sqlserver_name_ro,
							app.[app_name] sql_description
				FROM		[dbo].[tblMSX_server_discovery] s
				JOIN		[dbo].[tblMSX_server_sql_info] sq ON s.ssd_id = sq.sql_ssd_id
				JOIN		[dbo].[tblMSX_application] app ON s.[ssd_app_id] = app.[app_id]
				LEFT JOIN	[dbo].[tblMSX_sql_version] sv ON sq.sql_version_major_ro = sv.ver_major_ro
							AND sq.sql_version_minor_ro = sv.ver_minor_ro
				WHERE		s.ssd_is_disabled = 0
							AND s.ssd_is_vendor_managed_ro = 0
				) tServ


	--- Create the Groups (Non-MSX, Environment then SQL Version)
	INSERT INTO #centraldba_server_groups (name, description, server_type, parent_id, is_system_object)
		VALUES (N'Not MSX Managed', N'', 0, 1, 0)
	INSERT INTO #centraldba_server_groups (name, description, server_type, parent_id, is_system_object)
		SELECT		DISTINCT
					ssd_type_ro name,
					N'' description,
					0 server_type,
					(
					SELECT		server_group_id
					FROM		#centraldba_server_groups
					WHERE		name = N'Not MSX Managed'
								AND ISNULL(parent_id, 0) = 1
					) parent_id,
					0 is_system_object
		FROM		[dbo].[tblMSX_server_discovery] s
		JOIN		[dbo].[tblMSX_server_sql_info] sq ON s.ssd_id = sq.sql_ssd_id
		LEFT JOIN	[dbo].[tblMSX_sql_version] sv ON sq.sql_version_major_ro = sv.ver_major_ro
					AND sq.sql_version_minor_ro = sv.ver_minor_ro
		WHERE		s.ssd_is_disabled = 0
					AND s.ssd_is_vendor_managed_ro = 0
					AND ISNULL(sq.sql_has_master_jobs, 1) = 0
	INSERT INTO #centraldba_server_groups (name, description, server_type, parent_id, is_system_object)
		SELECT 		DISTINCT
					(CASE WHEN ISNULL(sv.ver_description, SPACE(0)) = SPACE(0) THEN 'Unknown' ELSE sv.ver_description END) name,
					N'' description,
					0 server_type,
					(
					SELECT		t2.server_group_id
					FROM		#centraldba_server_groups t1
					JOIN		#centraldba_server_groups t2
									ON t1.server_group_id = t2.parent_id
					WHERE		t1.name = N'Not MSX Managed'
								AND ISNULL(t1.parent_id, 0) = 1
								AND t2.name = s.ssd_type_ro
					) parent_id,
					0 is_system_object
		FROM		[dbo].[tblMSX_server_discovery] s
		JOIN		[dbo].[tblMSX_server_sql_info] sq ON s.ssd_id = sq.sql_ssd_id
		LEFT JOIN	[dbo].[tblMSX_sql_version] sv ON sq.sql_version_major_ro = sv.ver_major_ro
					AND sq.sql_version_minor_ro = sv.ver_minor_ro
		WHERE		s.ssd_is_disabled = 0
					AND s.ssd_is_vendor_managed_ro = 0
					AND ISNULL(sq.sql_has_master_jobs, 1) = 0

	--- Insert the Servers into the Groups (MSX Managed then SQL Version)
	INSERT INTO #centraldba_servers (server_group_id, name, server_name, description, server_type)
		SELECT	(
				SELECT		t3.server_group_id
				FROM		#centraldba_server_groups t1
				JOIN		#centraldba_server_groups t2
								ON t1.server_group_id = t2.parent_id
				JOIN		#centraldba_server_groups t3
								ON t2.server_group_id = t3.parent_id
				WHERE		t1.name = N'Not MSX Managed'
							AND ISNULL(t1.parent_id, 0) = 1
							AND t2.name = tServ.ssd_type_ro
							AND t3.name = tServ.SQLServer_description
				) server_group_id, 
				ssd_sqlserver_name_ro name, 
				ssd_sqlserver_name_ro server_name, 
				ISNULL(sql_description, SPACE(0)) description,
				0 server_type
		FROM	(
				SELECT 		s.ssd_type_ro,
							(CASE WHEN ISNULL(sv.ver_description, SPACE(0)) = SPACE(0) THEN 'Unknown' ELSE sv.ver_description END) SQLServer_description,
							s.ssd_sqlserver_name_ro,
							app.[app_name] sql_description
				FROM		[dbo].[tblMSX_server_discovery] s
				JOIN		[dbo].[tblMSX_server_sql_info] sq ON s.ssd_id = sq.sql_ssd_id
				JOIN		[dbo].[tblMSX_application] app ON s.[ssd_app_id] = app.[app_id]
				LEFT JOIN	[dbo].[tblMSX_sql_version] sv ON sq.sql_version_major_ro = sv.ver_major_ro
							AND sq.sql_version_minor_ro = sv.ver_minor_ro
				WHERE		s.ssd_is_disabled = 0
							AND s.ssd_is_vendor_managed_ro = 0
							AND ISNULL(sq.sql_has_master_jobs, 1) = 0
				) tServ

	--- Update msdb
	PRINT 'Updating the msdb list of servers'
	DECLARE @sSQL nvarchar(MAX)
	SELECT	@sSQL = N'USE [msdb];

	--- Remove the current records from sysmanagement
	TRUNCATE TABLE [dbo].[sysmanagement_shared_registered_servers_internal]
	DELETE FROM [dbo].[sysmanagement_shared_server_groups_internal] WHERE is_system_object <> 1
	DBCC CHECKIDENT(''[dbo].[sysmanagement_shared_server_groups_internal]'', RESEED, 5)

	--- Repopulate the Tables
	SET IDENTITY_INSERT [dbo].[sysmanagement_shared_server_groups_internal] ON
	INSERT INTO [dbo].[sysmanagement_shared_server_groups_internal] (server_group_id, name, description, server_type, parent_id, is_system_object)
		SELECT		server_group_id,
					name,
					description,
					server_type,
					parent_id,
					is_system_object
		FROM		#centraldba_server_groups
	SET IDENTITY_INSERT [dbo].[sysmanagement_shared_server_groups_internal] OFF

	SET IDENTITY_INSERT [dbo].[sysmanagement_shared_registered_servers_internal] ON
	INSERT INTO [dbo].[sysmanagement_shared_registered_servers_internal] (server_id, server_group_id, name, server_name, description, server_type)
		SELECT		server_id,
					server_group_id,
					name,
					server_name,
					description,
					server_type
		FROM		#centraldba_servers
	SET IDENTITY_INSERT [dbo].[sysmanagement_shared_registered_servers_internal] OFF'
	EXEC	(@sSQL)
END TRY
BEGIN CATCH
	SELECT
		ERROR_NUMBER() AS ErrorNumber
		,ERROR_SEVERITY() AS ErrorSeverity
		,ERROR_STATE() AS ErrorState
		,ERROR_PROCEDURE() AS ErrorProcedure
		,ERROR_LINE() AS ErrorLine
		,ERROR_MESSAGE() AS ErrorMessage;
END CATCH

--- Cleanup temp tables
PRINT 'Dropping temporary tables'
DROP TABLE #centraldba_server_groups
DROP TABLE #centraldba_servers

SET NOCOUNT OFF
